package AdminController.AdminMem;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import Connect.connector;
import MemberController.MembersBean;


public class AdminMemDAO {

	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	
	public int memberCount(int level){
		conn = connector.getConnection();
		
		try{
			
			String sql = "select count(*) from MEMBERS where Mem_Level=?";
			
			pstmt = conn.prepareStatement( sql );
			pstmt.setInt(1, level);
			rs = pstmt.executeQuery();
			
			if( rs.next()){
				
				int count = rs.getInt("count(*)");
				return count;
			}
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return 0;
	}
	
	
	
	
	public int memberCount(String mypage, int level){
		conn = connector.getConnection();
		
		try{
			
			String sql = "select count(*) from MEMBERS where Blogs_Id is "+mypage+" && Mem_Level=?";
			
			pstmt = conn.prepareStatement( sql );
			pstmt.setInt(1, level);
			rs = pstmt.executeQuery();
			
			if( rs.next()){
				
				int count = rs.getInt("count(*)");
				return count;
			}
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return 0;
	}
	
	public int checkMemberCount(String check, String table, String age, int level){
		conn = connector.getConnection();
		int checknum;
		try{
			checknum = Integer.parseInt(check);
System.out.println("값 = " + checknum);		
			String sql = "select count(*) from "+table+" a,MEMBERS b where a.Members_Id = b.ID and a."+age+"_Check=?  && b.Mem_Level=?";
			
			pstmt = conn.prepareStatement( sql );
			pstmt.setInt(1, checknum);
			pstmt.setInt(2, level);
			rs = pstmt.executeQuery();
			
			if( rs.next()){
				
				int count = rs.getInt("count(*)");
				return count;
			}
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return 0;
	}
	
	public AdminBean[] memberList(int level, int number, int size){
		conn = connector.getConnection();
		ArrayList<AdminBean> memberlist = new ArrayList<AdminBean>();
		int startnum;	
		try{
			
			if(number == 1){
				startnum = 0;
			}else{
				startnum = (number-1)*size;
			}
			
			String sql = "select * from (select * from MEMBERS where Mem_Level=? ORDER BY Mem_Date DESC) a where a.ID limit ?, ?";
			
			pstmt = conn.prepareStatement( sql );
			pstmt.setInt(1, level);
			pstmt.setInt(2, startnum);
			pstmt.setInt(3, size);
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				AdminBean adminbean= new AdminBean();
				
				adminbean.setID(rs.getInt("ID"));
				adminbean.setMem_Nick(rs.getString("Mem_Nick"));
				adminbean.setBlogs_Id(rs.getInt("Blogs_Id"));
				adminbean.setMem_Level(rs.getInt("Mem_Level"));
				memberlist.add(adminbean);
			}
				
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return memberlist.toArray(new AdminBean[0]);
	}
	
	public AdminBean[] memberList(String nick, int number, int size){
		conn = connector.getConnection();
		ArrayList<AdminBean> memberlist = new ArrayList<AdminBean>();
		int startnum;	
		try{
			
			if(number == 1){
				startnum = 0;
			}else{
				startnum = (number-1)*size;
			}
			
			String sql = "select * from (select * from MEMBERS where Mem_Nick=? ORDER BY Mem_Date DESC) a where a.ID limit ?, ?";
			
			pstmt = conn.prepareStatement( sql );
			pstmt.setString(1, nick);
			pstmt.setInt(2, startnum);
			pstmt.setInt(3, size);
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				AdminBean adminbean= new AdminBean();
				
				adminbean.setID(rs.getInt("ID"));
				adminbean.setMem_Nick(rs.getString("Mem_Nick"));
				adminbean.setBlogs_Id(rs.getInt("Blogs_Id"));
				adminbean.setMem_Level(rs.getInt("Mem_Level"));
				memberlist.add(adminbean);
			}
				
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return memberlist.toArray(new AdminBean[0]);
	}
	
	public AdminBean[] mypageMemberList(int level, String mypage, int number, int size){
		conn = connector.getConnection();
		ArrayList<AdminBean> memberlist = new ArrayList<AdminBean>();
		int startnum;	
		try{
			
			if(number == 1){
				startnum = 0;
			}else{
				startnum = (number-1)*size;
			}
			
			String sql = "select * from (select * from MEMBERS where Blogs_Id is "+mypage+" && Mem_Level=? ORDER BY Mem_Date DESC) a where a.ID limit ?, ?";
			
			pstmt = conn.prepareStatement( sql );
			pstmt.setInt(1, level);
			pstmt.setInt(2, startnum);
			pstmt.setInt(3, size);
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				AdminBean adminbean= new AdminBean();
				
				adminbean.setID(rs.getInt("ID"));
				adminbean.setMem_Nick(rs.getString("Mem_Nick"));
				adminbean.setBlogs_Id(rs.getInt("Blogs_Id"));
				adminbean.setMem_Level(rs.getInt("Mem_Level"));
				memberlist.add(adminbean);
			}
				
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return memberlist.toArray(new AdminBean[0]);
	}
	
	public AdminBean[] checkMemberList(int level, String table, String age, String check , int number, int size){
		conn = connector.getConnection();
		ArrayList<AdminBean> memberlist = new ArrayList<AdminBean>();
		int startnum;	
		int checknum;
		try{
			checknum = Integer.parseInt(check);
			if(number == 1){
				startnum = 0;
			}else{
				startnum = (number-1)*size;
			}
			
			String sql = "select b.ID,b.Blogs_Id,b.Mem_Nick,b.Mem_Level from "+table+" a,MEMBERS b where a.Members_Id = b.ID and b.Mem_Level=? and a."+age+"_Check = ? limit ?,?";
			
			pstmt = conn.prepareStatement( sql );
			pstmt.setInt(1, level);
			pstmt.setInt(2, checknum);
			pstmt.setInt(3, startnum);
			pstmt.setInt(4, size);
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				AdminBean adminbean= new AdminBean();
				
				adminbean.setID(rs.getInt("ID"));
				adminbean.setMem_Nick(rs.getString("Mem_Nick"));
				adminbean.setBlogs_Id(rs.getInt("Blogs_Id"));
				adminbean.setMem_Level(rs.getInt("Mem_Level"));
				memberlist.add(adminbean);
			}
				
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return memberlist.toArray(new AdminBean[0]);
	}
	
	public int checkGet(String table, String column, int id){
		conn = connector.getConnection();
		
		try{
			
			String sql = "select * from "+table+" where Members_Id = ? ORDER BY "+column+"_Date DESC;";
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setInt(1, id);
			
			rs = pstmt.executeQuery();
			
			if( rs.next()){
				
				int count = rs.getInt(column+"_Check");
				return count;
			}
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return 0;
	}
	public int failLevel(int id){
		conn = connector.getConnection();
		try{
			
			String sql = "select Mem_Level from MEMBERS where ID=?";
			
			System.out.println("*****레벨구별시작*****");
			System.out.println(id);
		
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setInt(1,id);
		
			
			rs = pstmt.executeQuery();
			
			while ( rs.next() ) {
				
				if(rs.getInt("Mem_Level")==1){
				System.out.println("*****할배/할매 회원*****");	
				return 1;
				}else if(rs.getInt("Mem_Level")==2){
				System.out.println("*****피도안마른애들*****");	
				return 2;
				}
			}
		}catch( Exception e ){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
	return -1;
}
	public void deletY(int id){
		conn = connector.getConnection();
		
		try{
			
			String sql = "delete from ABLILTY where Members_Id = ?";
			pstmt = conn.prepareStatement( sql );
			pstmt.setInt(1, id);
			pstmt.executeUpdate();
			
			sql = "delete from ABLILTYSUM where Members_Id = ?";
			pstmt = conn.prepareStatement( sql );
			pstmt.setInt(1, id);
			pstmt.executeUpdate();
			
			sql = "delete from ADMINYOUNG where Mem_Id = ?";
			pstmt = conn.prepareStatement( sql );
			pstmt.setInt(1, id);
			pstmt.executeUpdate();
			
			sql = "delete from BABYPLUS where Members_Id = ?";
			pstmt = conn.prepareStatement( sql );
			pstmt.setInt(1, id);
			pstmt.executeUpdate();
			
			sql = "delete from DAYCHECK where Members_Id = ?";
			pstmt = conn.prepareStatement( sql );
			pstmt.setInt(1, id);
			pstmt.executeUpdate();
			
			sql = "delete from DAYCHECKSUM where Members_Id = ?";
			pstmt = conn.prepareStatement( sql );
			pstmt.setInt(1, id);
			pstmt.executeUpdate();
			
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
	}
	
	public void deletO(int id){
		conn = connector.getConnection();
		
		try{
			
			String sql = "delete from ABLILTY where Members_Id = ?";
			pstmt = conn.prepareStatement( sql );
			pstmt.setInt(1, id);
			pstmt.executeUpdate();
			
			sql = "delete from ABLILTYSUM where Members_Id = ?";
			pstmt = conn.prepareStatement( sql );
			pstmt.setInt(1, id);
			pstmt.executeUpdate();
			
			sql = "delete from ADMINOLD where Mem_Id = ?";
			pstmt = conn.prepareStatement( sql );
			pstmt.setInt(1, id);
			pstmt.executeUpdate();
			
			sql = "delete from OLDPLUS where Members_Id = ?";
			pstmt = conn.prepareStatement( sql );
			pstmt.setInt(1, id);
			pstmt.executeUpdate();
			
			sql = "delete from DAYCHECK where Members_Id = ?";
			pstmt = conn.prepareStatement( sql );
			pstmt.setInt(1, id);
			pstmt.executeUpdate();
			
			sql = "delete from DAYCHECKSUM where Members_Id = ?";
			pstmt = conn.prepareStatement( sql );
			pstmt.setInt(1, id);
			pstmt.executeUpdate();
			
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
	}
	
	
	
	public String shareGet(int id){
		conn = connector.getConnection();
		
		try{
			
			String sql = "select Blog_Share from BLOGS where Members_Id=?";
			
			pstmt = conn.prepareStatement( sql );
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();
			
			if( rs.next()){
				
				String val = rs.getString("Blog_Share");
				return val;
			}
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return null;
	}
	
	public int mistressGet(String nick){
		conn = connector.getConnection();
		
		try{
			
			String sql = "select Members_Id from BLOGS where Blog_Share=?";
			
			pstmt = conn.prepareStatement( sql );
			pstmt.setString(1, nick);
			rs = pstmt.executeQuery();
			
			if( rs.next()){
				
				int val = rs.getInt("Members_Id");
				return val;
			}
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return -1;
	}
	
	public boolean blog(String nick){
		conn = connector.getConnection();
		
		try{
			
			String sql = "select Blogs_Id from MEMBERS where Mem_Nick=?";
			
			pstmt = conn.prepareStatement( sql );
			pstmt.setString(1, nick);
			rs = pstmt.executeQuery();
			
			if( rs.next()){
				
				return true;
			}
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return false;
	}
	
	public int levelGet (int id){
		conn = connector.getConnection();
		int level = 0;
		try{
		
			
			String sql = "select * from MEMBERS where ID = ?";
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setInt(1, id);
			
			rs = pstmt.executeQuery();
			
			if( rs.next()){
				
				level = rs.getInt("Mem_Level");

			}
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return level;
	}
	
	public int mapping(int id, String column, String column2){
		conn = connector.getConnection();
		
		try{
			
			String sql = "select * from MAPPING where "+column+"=?";
			
			pstmt = conn.prepareStatement( sql );
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();
			
			if( rs.next()){
				
				int count = rs.getInt(column2);
				return count;
			}
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return -1;
	}
	
	public String youNickGet(int id){
		conn = connector.getConnection();
		try{

			String sql = "select * from MEMBERS where ID = ?";
				
			pstmt = conn.prepareStatement( sql );
				
			pstmt.setInt(1, id);
				
			rs = pstmt.executeQuery();
				
			if( rs.next()){
				String younick = rs.getString("Mem_Nick");
				return younick;
			}
			
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return "";
	}
}
